package com.info.zhiduoduo.repository.mapper.company;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.company.*;
import com.info.zhiduoduo.repository.dto.evaluate.EvaluateNoticeInfoDto;
import com.info.zhiduoduo.repository.entity.company.Company;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author TerryJ
 * @since 2018-12-08
 */
public interface CompanyMapper extends BaseMapper<Company> {

    @Select(" select c.compid,compname,nickname,regphone,avatar,state, " +
            " (select ar.reason from zdd_company_auth_record ar " +
            " where ar.compid = c.compid order by ar.id desc limit 1) as authReason " +
            " from zdd_company c " +
            " where openid = #{openid}")
    Company getCompanyByOpenid(@Param("openid") String openid);

    @Select("select * from zdd_company where regphone = #{phone}")
    Company checkCompanyExistByPhone(@Param("phone") String phone);

    @Select(" select * from zdd_company where compname = #{compname} and state = 4 order by compid desc limit 1")
    Company getByCompname(@Param("compname") String compname);

    @Select(" select c.compid,c.compname,c.compnickname,c.regphone,ca.verify_phone,c.invite_code," +
            " c.crowdsource_project_match,c.special_rate_flag,ca.show_zhaoshang_ledger,ca.marketing_cooperative," +
            " ca.marketing_cooperative_qrcode,c.open_odd_job,c.open_zdd,ca.trade_flag,c.view_maker_quota," +
            " c.intel_crowdsource,c.project_service_rate,c.base_tax_rate,c.low_tax_rate,c.high_tax_rate, " +
            " ca.maker_groupon,c.entrusted_receipt_status,c.open_zdd,ca.trade_flag,ca.no_feel_income " +
            " from zdd_company c " +
            " left join zdd_company_account ca on ca.compid = c.compid " +
            " where c.compid = #{compid} " +
            " order by c.compid DESC " )
    CompanyBriefDto getBriefByCompid(int compid);

    @Select(" select c.*,ca.acctid,ca.merchantid,ca.md5key,ca.merchant_request_seq,ca.realname,ca.idno," +
            " ca.verify_phone,ap.name contactprovince,ac.name contactcity,aa.name contactarea,ca.shareholder, " +
            " ca.idfrontimg,ca.idbackimg,ca.invalid_date,ca.issue_date,ca.agree_protocol,ca.strategy_invoice,ca.change_notes," +
            " ca.today_amount_reminder,ca.business_manager_id,ca.economy_category_id,ca.core_module," +
            " bm.business_manager,bm.nickname as businessNickname,bm.phone as businessPhone,pac.realname as crowdsourcePartnerName," +
            " IF(IFNULL(ca.pay_password,'')='',0,1) as hasPayPwd,ca.whether_subject, " +
            " ca.maker_groupon,pa.promo_code as invitCode,pa.realname as inviter," +
            " ca.show_zhaoshang_ledger,ca.marketing_cooperative,ca.marketing_cooperative_qrcode " +
            " from zdd_company c " +
            " left join zdd_company_account ca on ca.compid = c.compid " +
            " left join zdd_address ap on ap.id = c.contactprovinceid " +
            " left join zdd_address ac on ac.id = c.contactcityid " +
            " left join zdd_address aa on aa.id = c.contactareaid " +
            " left join zdd_partner_account pa on pa.promo_code = c.invite_code and c.invite_code != '' " +
            " left join zdd_partner_account pac on pac.partnerid = c.crowdsource_partner_id " +
            " left join zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id " +
            " where c.compid = #{compid}")
    CompanyDetailDto getCompanyInfo(@Param("compid") Integer compid);

    @Select(" SELECT cr.createtime,cr.rechargeno,cr.receiptimg,c.compname,cr.receiptno,cr.amount  " +
            " FROM zdd_company_recharge cr  " +
            " JOIN zdd_company c ON c.compid = cr.compid  " +
            " WHERE cr.compid = #{compid}  " +
            " AND cr.createtime <= #{createtime}  " +
            " ORDER BY cr.createtime DESC  " +
            " LIMIT 1 ")
    RechargeReceiptDto getRechargeReceipt(@Param("compid") int compid ,@Param("createtime") Date createtime);

    @Select(" select c.*," +
            " ca.verify_phone,ca.economy_category_id,ca.core_module,ca.strategy_invoice,ca.maker_groupon,ca.merchandiser_id," +
            " ca.contract_start_date,ca.contract_end_date,ca.show_zhaoshang_ledger,ca.marketing_cooperative,ca.marketing_cooperative_qrcode, " +
            " ca.account_link_id,ca.normal_link_ids,bmm.business_manager as merchandiserName,pa.realname as inviter,bm.business_manager ," +
            " count(mem.content) as needCount,if(isnull(inc.id),0,1) as isPart," +
            " cef.entrust_end_time,cef.entrust_file,cef.entrust_start_time, " +
            " CASE WHEN cef.entrust_end_time is null THEN 1 when cef.entrust_end_time < now() then 3 ELSE 2 END as entrustFileState  " +
            " from zdd_company c " +
            " left join zdd_partner_account pa on pa.promo_code = c.invite_code and c.invite_code != '' " +
            " left join zdd_company_account ca on ca.compid = c.compid " +
            " left join zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id  " +
            " left join zdd_business_manager bmm on bmm.business_manager_id = ca.merchandiser_id  " +
            " left join zdd_platform_memorandum mem on mem.customer_id=c.compid " +
            " left join zdd_crm_incoming inc on inc.compid=c.compid " +
            " left join (select compid,MAX(entrust_end_time) AS entrust_end_time,entrust_file,entrust_start_time from zdd_company_entrust_file WHERE state = 1 and deleted = 0 GROUP BY compid ORDER BY entrust_end_time desc) cef on c.compid = cef.compid " +
            " where if(ISNULL(#{compid}) or #{compid}=0 ,1=1,c.compid = #{compid}) " +
            " and if(#{state} = 0,c.state != 1,c.state = #{state}) " +
            " and concat(c.compname,c.contactname,c.contactphone,if(ca.change_notes is null,'',ca.change_notes)) like '%${name}%' " +
            " AND IF(ISNULL(#{inviter}) or #{inviter}='',1=1,CONCAT(pa.promo_code,pa.realname) like '%${inviter}%') " +
            " AND IF(ISNULL(#{businessManager}) OR #{businessManager}='', 1=1, bm.business_manager like '%${businessManager}%' ) " +
            " AND IF(ISNULL(#{strategyInvoice}), 1=1, ca.strategy_invoice = #{strategyInvoice}) " +
            " AND IF(ISNULL(#{tradeFlag}), 1=1, ca.trade_flag = #{tradeFlag}) " +
            " AND IF(ISNULL(#{specialRateFlag}), 1=1, c.special_rate_flag = #{specialRateFlag}) " +
            " AND IF(ISNULL(#{quotaFlag}), 1=1, c.quota_flag = #{quotaFlag}) " +
            " AND IF(#{crowdsource}=0, 1=1, c.intel_crowdsource = #{crowdsource} )" +
            " and if(isnull(#{controlFill}),1=1,inc.meanwhile_control=#{controlFill})" +
            " and if(isnull(#{standard}),1=1,ca.standard=#{standard})" +
            " and if(isnull(#{friendlyState}),1=1,c.friendly_state=#{friendlyState})" +
            " and c.createtime between #{startDate} and #{endDate} " +
            " and if(isnull(#{activeRegister}),1=1,if(#{activeRegister}=0,c.openid='',c.openid!='')) " +
            " and if(ISNULL(#{merchandiserId}),1=1,ca.merchandiser_id = #{merchandiserId}) " +
            " and if(ISNULL(#{agentOperationAccount}),1=1,c.agent_operation_account = #{agentOperationAccount}) " +
            " and if(ISNULL(#{regPhone}) or #{regPhone} ='' ,1=1,c.regphone = #{regPhone}) " +
            " and if(ISNULL(#{marketingCooperative}),1=1,ca.marketing_cooperative = #{marketingCooperative}) " +
            " and if(isnull(#{accountLinkIds}),1=1,ca.account_link_id in (0${accountLinkIds}))" +
            " group by c.compid"+
            " having IF(isnull(#{entrustFileState}),1=1,entrustFileState = #{entrustFileState})" +
            " order by c.compid DESC ")
    List<CompanyVerifyBriefDto> getCompanyList(@Param("name") String name,@Param("compid") Integer compid, @Param("inviter") String inviter,
                                               @Param("businessManager") String businessManager, @Param("state") int state,
                                               @Param("crowdsource") int crowdsource, @Param("strategyInvoice") Integer strategyInvoice,
                                               @Param("startDate") Date startDate, @Param("endDate") Date endDate,
                                               @Param("specialRateFlag") Integer specialRateFlag,
                                               @Param("activeRegister") Integer activeRegister,@Param("controlFill") Integer controlFill,
                                               @Param("quotaFlag") Integer quotaFlag,@Param("standard")Integer standard,
                                               @Param("friendlyState")Integer friendlyState,@Param("merchandiserId") Integer merchandiserId,
                                               @Param("agentOperationAccount") Integer agentOperationAccount,
                                               @Param("entrustFileState") Integer entrustFileState,
                                               @Param("regPhone") String regPhone,
                                               @Param("marketingCooperative") Integer marketingCooperative,@Param("tradeFlag") Integer tradeFlag,
                                               @Param("accountLinkIds") String accountLinkIds);

    @Select(" select c.compid,c.compname,c.regphone,ca.verify_phone,c.invite_code,ca.show_zhaoshang_ledger," +
            " ca.marketing_cooperative,ca.marketing_cooperative_qrcode," +
            " c.crowdsource_project_match,c.special_rate_flag,ca.account_link_id, " +
            " c.intel_crowdsource,c.project_service_rate,c.base_tax_rate,c.low_tax_rate,c.high_tax_rate " +
            " from zdd_company c " +
            " left join zdd_company_account ca on ca.compid = c.compid " +
            " where compname like CONCAT('%',#{compname},'%') and c.state = 4 " +
            " order by c.compid DESC " )
    List<CompanyBriefDto> getCompanyBriefByName(@Param("compname") String compname);

    @Select(" select c.compid,c.compname,c.regphone,ca.verify_phone,ca.show_zhaoshang_ledger," +
            " ca.marketing_cooperative,ca.marketing_cooperative_qrcode," +
            " c.invite_code,c.crowdsource_project_match,c.special_rate_flag," +
            " c.intel_crowdsource,c.project_service_rate,c.base_tax_rate,c.low_tax_rate,c.high_tax_rate " +
            " from zdd_company c " +
            " left join zdd_company_account ca on ca.compid = c.compid " +
            " where compname like '%${compname}%' " +
            " and intel_crowdsource = 2 " +
            " and c.state = 4 " +
            " order by c.compid DESC ")
    List<CompanyBriefDto> getIntelCrowdsourceCompany(@Param("compname") String compname);

    @Select(" select c.compid,c.compname,c.regphone,ca.verify_phone,ca.show_zhaoshang_ledger," +
            " ca.marketing_cooperative,ca.marketing_cooperative_qrcode," +
            " c.invite_code,c.crowdsource_project_match,c.special_rate_flag,c.invoice_type," +
            " c.intel_crowdsource,c.project_service_rate,c.base_tax_rate,c.low_tax_rate,c.high_tax_rate " +
            " from zdd_company c " +
            " left join zdd_company_account ca on ca.compid = c.compid " +
            " where compname = #{compname} " +
            " and c.state = 4 " +
            " order by c.compid DESC ")
    List<CompanyBriefDto> getIntelCrowdsourceCompanyByName(@Param("compname") String compname);

    /**
     * 查询企业openid
     * @param intelCrowdsource 1 普通企业  2 智众包企业
     * @return
     */
    @Select(" select openid from zdd_company c " +
            " join zdd_company_account ca on ca.compid = c.compid " +
            " where c.state = 4 " +
            " and if(#{intelCrowdsource} = 0 ,1 = 1 , c.intel_crowdsource = #{intelCrowdsource}) " +
            " and ca.realname_auth = 2 and c.notice_flag = 0 ")
    List<String> getAllValidOpenids(int intelCrowdsource);

    /**
     * 查询评价公告所需的数据
     * @param intelCrowdsource 1 普通企业  2 智众包企业
     * @return
     */
    @Select(" select c.compid AS userId, c.openid AS openId, 2 AS userType from zdd_company c " +
            " where c.openid is not null and c.openid != '' " +
            " and if(#{intelCrowdsource} = 0 ,1 = 1 , c.intel_crowdsource = #{intelCrowdsource}) ")
    List<EvaluateNoticeInfoDto> getPushEvaluateNoticeNeedData(int intelCrowdsource);

    /**
     * 根据地区查询openid
     * @param residence 地区编号
     * @param flag 1 某个省市  1 整个省
     * @param intelCrowdsource 1 普通企业  2 智众包企业
     * @return String
     */
    @Select(" select openid from zdd_company c " +
            " join zdd_company_account ca on ca.compid = c.compid " +
            " where c.state = 4 and ca.realname_auth = 2 and c.notice_flag = 0 " +
            " and if(#{flag} = 1, c.headquarters = #{residence}, c.headquarters like '%${residence}%') " +
            " and if(#{intelCrowdsource} = 0 ,1 = 1 , c.intel_crowdsource = #{intelCrowdsource}) ")
    List<String> getAllValidOpenidsByHeadQuarters(String residence, int flag, int intelCrowdsource);

    @Select(" select count(distinct c.compid) from zdd_company c " +
            " join zdd_company_account ca on ca.compid = c.compid and ca.realname_auth = 2 " +
            " join zdd_partner_account pa on pa.promo_code = c.invite_code and pa.partnerid = #{partnerid} " +
            " where c.state = 4 " +
            " and YEAR(c.createtime) = YEAR(#{month}) " +
            " and QUARTER(c.createtime) = QUARTER(#{month}) ")
	int countPartnerInvited(int partnerid, Date month);


    /**
     * 获取 审核通过但从未交易过 塾人项目 或 智众包项目 的企业（草稿项目 删除的 取消的 项目 不算在内）
     * @return list
     */
    @Select(" SELECT c.compid,c.compname,ca.business_manager_id,bm.business_manager,ca.merchandiser_id " +
            " FROM zdd_company c " +
            " JOIN zdd_company_account ca ON ca.compid = c.compid " +
            " JOIN zdd_business_manager bm ON bm.business_manager_id = ca.business_manager_id " +
            " LEFT JOIN " +
            " (SELECT cp.compid,SUM( cp.projectdealamount ) AS dAmount FROM zdd_company_project cp WHERE cp.state NOT IN ( 1, 2, 40, 44 ) GROUP BY compid) " +
            " AS dcp ON dcp.compid = c.compid " +
            " LEFT JOIN " +
            " (SELECT csp.compid,SUM( csp.project_total_amount ) AS bAmount FROM zdd_crowdsource_project csp WHERE csp.state NOT IN ( 1, 99 ) AND csp.actual_sender IS NULL GROUP BY compid) " +
            " AS bcp ON bcp.compid = c.compid " +
            " LEFT JOIN " +
            " (SELECT ep.compid,SUM( ep.imported_amount ) AS cAmount FROM zdd_entrustment_project ep WHERE ep.state != 1  AND ep.actual_company_id IS NULL GROUP BY compid) " +
            " AS ccp ON ccp.compid = c.compid " +
            " WHERE" +
            " c.state = 4 AND ca.realname_auth = 2 " +
            " AND ca.whether_subject = 0 " +
            " AND dcp.dAmount IS NULL " +
            " AND bcp.bAmount IS NULL " +
            " AND ccp.cAmount IS NULL ")
    List<CompanyManagerDto> getNotTradeCompany();


    /**
     * 获取 注册后并审核通过 但上一个月 塾人项目 智众包项目 都没交易过的企业 （草稿项目 删除的 取消的 项目 不算在内）
     * @param lastMonth
     * @return
     */
    @Select(" SELECT c.compid,c.compname,ca.business_manager_id,bm.business_manager " +
            " FROM zdd_company c " +
            " JOIN zdd_company_account ca ON ca.compid = c.compid " +
            " JOIN zdd_business_manager bm ON bm.business_manager_id = ca.business_manager_id " +
            " LEFT JOIN " +
            " (SELECT cp.compid,SUM( cp.projectdealamount ) AS dAmount FROM zdd_company_project cp WHERE cp.state NOT IN ( 1, 2, 40, 44 ) AND YEAR ( cp.createtime ) = YEAR ( #{lastMonth} ) AND MONTH ( cp.createtime ) = MONTH ( #{lastMonth} )  GROUP BY compid) " +
            " AS dcp ON dcp.compid = c.compid " +
            " LEFT JOIN " +
            " (SELECT csp.compid,SUM( csp.project_total_amount ) AS bAmount FROM zdd_crowdsource_project csp WHERE csp.state NOT IN ( 1, 99 ) AND YEAR ( csp.createtime ) = YEAR ( #{lastMonth} ) AND MONTH ( csp.createtime ) = MONTH ( #{lastMonth} )  GROUP BY compid) " +
            " AS bcp ON bcp.compid = c.compid " +
            " WHERE" +
            " c.state = 4 AND ca.realname_auth = 2 " +
            " AND dcp.dAmount IS NULL " +
            " AND bcp.bAmount IS NULL ")
    List<CompanyManagerDto> noticeBusinessForOneMonthNotTradedCompany(Date lastMonth);

    /**
     * 通过企业id查看企业是否存在
     * @param companyId
     * @return
     */
    @Select("SELECT IFNULL(SUM(1),0) FROM zdd_company where compid = #{companyId}")
    Integer checkCompanyExistById(Integer companyId);
    
    @Select(" SELECT base.*,years,  " +
            "    IFNULL(SUM(zddAmount), 0) as zddAmount,  " +
            "    IFNULL(SUM(crowdsourceAmount), 0) as crowdsourceAmount,  " +
            "    IFNULL(SUM(entrustAmount), 0) as entrustAmount,  " +
            "    IFNULL(SUM(agentTaxerAmount), 0) as agentTaxerAmount  " +
            "FROM  " +
            "  (  " +
            "  SELECT com.compid, compname   " +
            "  FROM zdd_company com JOIN zdd_company_account acc ON acc.compid = com.compid   " +
            "  WHERE com.state = 4   " +
            "  GROUP BY com.compid  " +
            "    ) base   " +
            "  left join (  " +
            "    SELECT pro.compid, YEAR(pro.createtime) AS years, IFNULL(SUM(projectdealamount), 0) AS zddAmount,  " +
            "      0 AS crowdsourceAmount, 0 AS entrustAmount, 0 AS agentTaxerAmount   " +
            "    FROM zdd_company_project pro   " +
            "    WHERE state IN (10,12,13,15,20)   " +
            "    GROUP BY pro.compid, YEAR(pro.createtime)  " +
            "    union all    " +
            "    select csp.compid, YEAR(cspi.createtime) as years,0 AS zddAmount,IFNULL(SUM(cspi.integrated_tax_fee), 0)  AS crowdsourceAmount,  " +
            "          0 AS entrustAmount, 0 AS agentTaxerAmount  " +
            "    from zdd_crowdsource_project_item cspi   " +
            "    join zdd_crowdsource_project csp on csp.projectid = cspi.projectid and csp.state not in (98,99)   " +
            "    join zdd_crowdsource_trade_record_item cstri on cstri.project_item_id = cspi.id and repay = 0 and cstri.status in (1,2,3)   " +
            "    where cspi.deleted = 0  " +
            "    GROUP BY csp.compid,YEAR(cspi.createtime)  " +
            "    union all    " +
            "    select compid, YEAR(createtime) as years,0 AS zddAmount,0 AS crowdsourceAmount,      " +
            "           IFNULL(SUM(tax_inclusive_amount), 0) AS entrustAmount, 0 AS agentTaxerAmount  " +
            "    from zdd_entrustment_project_item   " +
            "    where state in (2,3,4)   " +
            "    group by compid,YEAR(createtime)  " +
            "    union all   " +
            "    select mak.bindcompid as compid,YEAR(ti.createtime) as years,0 AS zddAmount ,  0 AS crowdsourceAmount,  " +
            "        0 AS entrustZddAmount,IFNULL(SUM(ti.amount),0) as agentTaxerAmount  " +
            "    from zdd_taxer_invoice ti  " +
            "    left join zdd_taxer_payment tp on tp.payid = ti.payid   " +
            "    join zdd_maker_studio ms on ms.userid = ti.userid   " +
            "    left join zdd_maker mak on ms.userid = mak.userid  " +
            "    where ti.deleted = 0 and ms.taxer = 1   " +
            "    GROUP BY mak.bindcompid,YEAR(ti.createtime)  " +
            ") da on base.compid = da.compid  " +
            " where base.compid = #{compid} " +
            " and years = #{years} " +
            " GROUP BY base.compid,years ")
    CompanyTransAmountDto queryCompanyYearlyTransAmount(Integer compid,Integer years);

    @Select(" select count(*) from zdd_company where compid != #{compid} " +
            " and if(#{compnickname} = '', 1=0, compnickname = #{compnickname})")
	boolean checkNicknameExists(@Param("compid") int compid, @Param("compnickname") String compnickname);

    @Select(" select c.* from zdd_company c" +
            " left join zdd_company_account ca on ca.compid = c.compid " +
            " where " +
            " c.state = 4" +
            " AND c.auto_freeze = 0" +
            " and c.updatetime <= #{date} " +
            " and (not EXISTS(select * from zdd_company_project cp where cp.compid = c.compid and cp.createtime >= #{date})" +
            " and not EXISTS(select * from zdd_crowdsource_project csp where csp.compid = c.compid and csp.createtime >= #{date})" +
            " and not EXISTS(select * from zdd_entrustment_project ep where ep.compid = c.compid and ep.createtime >= #{date}))")
	List<Company> list3MonthsNoTrade(Date date);

    @Select(" select c.compid,c.state,c.crowdsource_partner_id,c.agent_operation_account,c.compname,c.regphone,c.invite_code,c.creditcode,c.contactname,c.contactphone,c.notice_flag,c.special_rate_flag,c.quota_flag,  " +
            " c.intel_crowdsource,c.project_service_rate,c.base_tax_rate,c.low_tax_rate,c.high_tax_rate,ca.verify_phone,c.crowdsource_project_match,c.friendly_state,c.nickname,  " +
            " c.createtime,c.updatetime,pa.realname as inviter,ca.economy_category_id,ca.core_module,ca.strategy_invoice,  " +
            " bm.business_manager as businessManager,ca.contract_start_date,ca.contract_end_date,  " +
            " ca.maker_groupon,ca.merchandiser_id,bmm.business_manager as merchandiserName,  " +
            " count(mem.content) as needCount,if(isnull(inc.id),0,1) as isPart,c.entrusted_receipt_status,  " +
            " cef.entrust_end_time,cef.entrust_file,cef.entrust_start_time,   " +
            " CASE WHEN cef.entrust_end_time is null THEN 1 when cef.entrust_end_time < now() then 3 ELSE 2 END as entrustFileState,    " +
            " ca.show_zhaoshang_ledger,ca.marketing_cooperative,ca.marketing_cooperative_qrcode,  " +
            " c.auto_freeze   " +
            " from zdd_company c   " +
            " left join zdd_partner_account pa on pa.promo_code = c.invite_code and c.invite_code != ''   " +
            " left join zdd_company_account ca on ca.compid = c.compid   " +
            " left join zdd_business_manager bm on bm.business_manager_id = ca.business_manager_id    " +
            " left join zdd_business_manager bmm on bmm.business_manager_id = ca.merchandiser_id    " +
            " left join zdd_platform_memorandum mem on mem.customer_id=c.compid  " +
            " left join zdd_crm_incoming inc on inc.compid=c.compid   " +
            " left join (select compid,MAX(entrust_end_time) AS entrust_end_time,entrust_file,entrust_start_time from zdd_company_entrust_file WHERE state = 1 and deleted = 0 GROUP BY compid ORDER BY entrust_end_time desc) cef on c.compid = cef.compid   " +
            " where c.state = 4 " +
            " group by c.compid " +
            " having entrustFileState IN(1,3) " +
            " order by c.compid DESC  ")
    List<CompanyVerifyBriefDto> exportVerifyList();

    @Select(" select c.openid from zdd_company c " +
            " where c.compid = #{companyId} ")
    String getValidOpenId(@Param("companyId") int companyId);

    @Select(" select c.compid,c.compname,c.regphone,ca.verify_phone,c.invite_code,ca.show_zhaoshang_ledger," +
            " ca.marketing_cooperative,ca.marketing_cooperative_qrcode," +
            " c.crowdsource_project_match,c.special_rate_flag,ca.account_link_id, " +
            " c.intel_crowdsource,c.project_service_rate,c.base_tax_rate,c.low_tax_rate,c.high_tax_rate " +
            " from zdd_company c " +
            " left join zdd_company_account ca on ca.compid = c.compid " +
            " where compname like CONCAT('%',#{compname},'%') and c.state = 4 " +
            " and c.limited_liability_company = 1 " +
            " order by c.compid DESC " )
    List<CompanyBriefDto> liabilityQueryCompany(@Param("compname") String compname);


    @Select(" SELECT  " +
            "  c.* ," +
            "  bm.business_manager AS businessManagerName " +
            " FROM  " +
            "  `zdd_company` c  " +
            " LEFT JOIN  zdd_company_account ca ON  ca.compid = c.compid " +
            " LEFT JOIN  zdd_business_manager bm ON  bm.business_manager_id = ca.business_manager_id " +
            " WHERE (c.open_odd_job = 1 OR c.intel_crowdsource = 2 ) " +
            " and IF(ISNULL(#{compname}) or #{compname}='', 1 = 1, c.compname = #{compname}) " +
            " and c.state = 4 " +
            " and IF(#{state} = 0  , 1 = 1, c.contract_status = #{state}) " +
            " ORDER BY c.contract_status  ")
    List<GetAnEnterpriseContractDto> getAnEnterpriseContract(@Param("compname") String compname,
                                                             @Param("state") int state);


    @Select(" SELECT  " +
            "  c.* " +
            " FROM  " +
            "  `zdd_company` c  " +
            " WHERE (c.open_odd_job = 1 OR c.intel_crowdsource = 2 ) " +
            " and c.state = 4 " +
            " ORDER BY c.createtime DESC ")
    List<Company> whetherTheContractHasExpired();

    @Select("select c.compid,c.compname from zdd_company c " +
            " left join zdd_company_account ca on c.compid = ca.compid " +
            " where ca.account_link_id = #{accountLinkId} ")
    List<Company> queryCompanyByAccountLinkId(int accountLinkId);
}
